<?php
declare (strict_types = 1);

namespace app\command\temp;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Db;

class FixAllTimeCommand extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('fixAllTime')
            ->setDescription('修改所有地方的时间戳');
    }

    protected function execute(Input $input, Output $output)
    {
        ini_set('memory_limit', '-1');
        $sql = 'SHOW TABLES';
        $tables = Db::query($sql);
        foreach($tables as $tableName) {
            $tableName = array_values($tableName)[0];
            
            var_dump('开始执行表'.$tableName.'处理');
            
            $sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '".$tableName."'";
            $columns = Db::query($sql);
            $timeFields = [];
            $columnsArr = [];
            $addFields = [];
            $fixField = [];
            $lastField  = '';
            //记录多少是时间字段
            foreach ($columns as $column) {
                $columnName   = array_values($column)[0];
                var_dump('开始执行字段'.$columnName.'处理');
                $columnsArr[] = $columnName;
                $needle = "_time";
                $pattern = '/_time$/i';
                if(preg_match($pattern,$columnName,$matches)) {
                    $timeFields[]   = $columnName;
                    $addFields[] = $columnName.'_stamp';
                    $fixField[$columnName] = $columnName.'_stamp';
                }
                
                $pattern = '/_time_stamp$/i';
                if(!preg_match($pattern,$columnName,$matches)) {
                    $lastField = $columnName;
                }
            }
            if(empty($addFields))continue;
            //增加字段
            foreach ($addFields as $field) {
                var_dump('开始增加字段'.$field.'处理');
                if(in_array($field, $columnsArr)){
//                     var_dump('字段'.$field.'存在，跳过');
//                     continue;
                    $sql = "ALTER TABLE {$tableName} DROP COLUMN {$field}";
                    Db::execute($sql);
                }
                if($lastField) {
                    $sql = "ALTER TABLE `{$tableName}` ADD COLUMN `{$field}` INT(11) UNSIGNED DEFAULT 0 NOT NULL AFTER `{$lastField}`";
                    Db::execute($sql);
                }
                var_dump('字段'.$field.'增加完成');
            }
            var_dump('--表'.$tableName.'结构改动完成--');
            var_dump('开始执行表'.$tableName.'数据处理');
            
            $count  = 10000;
            $offset = 0;
            $field  = "id,".implode(',', $timeFields);
            while (true) {
                $sql    = "select {$field} from {$tableName} limit {$offset},{$count}";
                var_dump($sql);
                $result = Db::query($sql);
                if(empty($result))break;
                foreach($result as $value) {
                    $set = [];
                    $setStr = '';
                    foreach($fixField as $befield => $updateFiled) {
                        $set[] = $updateFiled." = ".strtotime($value[$befield]);
                    }
                    if($value['id']) {
                        $sql = "update {$tableName} set ".implode(',', $set)." where id = {$value['id']}";
                        var_dump($sql);
                        Db::execute($sql);
                    }
                }
                
                $offset += $count;
            }
            
            
        }
        // 指令输出
        $output->writeln('成功');
    }
}
